package excel

import (
	"encoding/csv"
	"errors"
	"fmt"
	"github.com/goravel/framework/contracts/filesystem"
	"github.com/goravel/framework/contracts/http"
	"github.com/goravel/framework/facades"
	"github.com/goravel/framework/support/carbon"
	"github.com/goravel/framework/support/path"
	"github.com/spf13/cast"
	"github.com/xuri/excelize/v2"
	"goravel/app/models"
	"io"
	"os"
	"path/filepath"
	"regexp"
	"time"
)

type Excelservice struct{}

func NewExcelService() *Excelservice {
	return &Excelservice{}
}

type LoadFile struct {
	Name string `json:"name"`
	Type string `json:"type"`
}

// 获取路径下的所有文件，提取文件名，返回LoadFile数组
func (*Excelservice) GetFiles() ([]LoadFile, error) {
	//获取到public目录
	dir, _ := os.Getwd()
	//判断如果是windows系统，就把\替换成/,public目录下的excel目录为excel文件夹
	if os.IsPathSeparator('\\') {
		dir = dir + "\\public\\excel\\"
	} else {
		dir = dir + "/public/excel/"
	}
	dirPth := dir //获取当前目录
	files := make([]LoadFile, 1)
	dir, err := filepath.Abs(dirPth)
	if err != nil {
		return nil, err
	}
	filepath.Walk(dir, func(filename string, fi os.FileInfo, err error) error {
		if err != nil {
			return err
		}
		if fi.IsDir() {
			return nil
		}
		//后缀名
		ext := filepath.Ext(fi.Name())
		files = append(files, LoadFile{
			Name: fi.Name(),
			Type: ext, //文件后缀名
		})
		return nil
	})
	//从files中提取文件名，删除后缀
	for i, file := range files {

		if filepath.Ext(file.Type) == ".xlsx" {
			files[i].Name = file.Name[0 : len(file.Name)-5]
		}
		if filepath.Ext(file.Type) == ".xls" {
			files[i].Name = file.Name[0 : len(file.Name)-4]
		}
		if filepath.Ext(file.Type) == ".csv" {
			files[i].Name = file.Name[0 : len(file.Name)-4]
		}
	}
	//过滤掉files中的file中的Name为空的元素
	var files2 []LoadFile
	for _, file := range files {
		if file.Name != "" {
			files2 = append(files2, file)
		}
	}
	//判断files2是否为空
	if len(files2) == 0 {
		err33 := errors.New("无文件，请放入指定文件")
		return nil, err33
	}
	return files2, nil
}

// ReadStreamExcel 读取流式excel
func (*Excelservice) ReadStreamExcel() error {
	loadFiles, err0 := NewExcelService().GetFiles()
	if err0 != nil {
		return err0
	}

	dir, _ := os.Getwd()
	cols := make([]string, 0)
	//1、先读取表头，也就是.xlsx或者.xls文件的第一行

	for _, loadFile := range loadFiles {
		if filepath.Ext(loadFile.Type) == ".xlsx" || filepath.Ext(loadFile.Type) == ".xls" {
			//判断操作系统，调整路径
			header, err := NewExcelService().GetHeader(loadFile.Name, loadFile.Type)
			if err != nil {
				return err
			} else {
				cols = header
			}
			//创建表
			error22 := NewExcelService().CrtTable(loadFile.Name, cols)
			if error22 != nil {
				return error22
			}
		}
	}
	//	2、再读取内容，也就是.csv文件的所有内容
	for _, loadFile := range loadFiles {
		if filepath.Ext(loadFile.Type) == ".csv" {

			//判断操作系统
			var dirPath string
			if os.IsPathSeparator('\\') {
				dirPath = dir + "\\public\\excel\\" + loadFile.Name + loadFile.Type
			} else {
				dirPath = dir + "/public/excel/" + loadFile.Name + loadFile.Type
			}
			//流式读取csv
			csvFile, err := os.Open(dirPath)
			if err != nil {
				return err
			}
			defer csvFile.Close()
			//提示信息
			fmt.Println("开始读取csv文件..." + loadFile.Name + loadFile.Type)
			//判断有多少个sheet，循环读取
			reader := csv.NewReader(csvFile)

			for i := 0; ; i++ {
				if row, err := reader.Read(); err == nil {
					NewExcelService().InstCol(loadFile.Name, cols, row)
					//打印进度/|\- 提示正在运行,/|\-是转圈的动画
					fmt.Printf("\r正在导入数据...%v", []string{"/", "|", "\\", "-"}[i%4])
				} else {
					if err == io.EOF {
						break
					}
					return err
				}
			}
			//当读取完毕后，打印读取完毕提示
			fmt.Println("\n🏆数据导入完毕！表名：" + loadFile.Name)

		}
	}
	return nil
}

// CrtTable 创建数据库表,第一参数为表名，第二个参数[]string为表头
func (*Excelservice) CrtTable(tablename string, cols []string) error {
	crtSql := "create table " + tablename + "("
	//crtSql += "id bigint identity(1,1) primary key, "
	for _, col := range cols {
		crtSql += "\"" + col + "\"" + " varchar(255) DEFAULT NULL" + ","
	}
	crtSql = crtSql[0:len(crtSql)-1] + ")"
	//日志打印
	//facades.Log.Info(crtSql)
	//执行sql语句
	_, err := facades.Orm().Query().Exec(crtSql)
	if err != nil {
		return err
	}
	return nil
}

// InstCol 插入数据:创建数据库表,第一参数为表名，第二个参数[]string为表头,第三个参数为数据
func (*Excelservice) InstCol(tablename string, cols []string, vals []string) {
	instSql := "INSERT INTO " + tablename + " ("
	for _, col := range cols {
		instSql += "\"" + col + "\"" + ","
	}
	instSql = instSql[0:len(instSql)-1] + ") VALUES ("
	for _, val := range vals {
		instSql += "N'" + val + "',"
	}
	instSql = instSql[0:len(instSql)-1] + ");\n"

	facades.Orm().Query().Exec(instSql)
}

func (*Excelservice) GetHeader(name string, filetype string) ([]string, error) {
	var dirPath string
	dir, _ := os.Getwd()
	cols := make([]string, 0)

	if os.IsPathSeparator('\\') {
		dirPath = dir + "\\public\\excel\\" + name + filetype
	} else {
		dirPath = dir + "/public/excel/" + name + filetype
	}
	file, err1 := os.Open(dirPath)
	if err1 != nil {
		return nil, err1
	}
	//流式读取excel
	f, err2 := excelize.OpenReader(file)
	if err2 != nil {
		return nil, err2
	}
	//读取表的第一行作为表头，在创建该表
	rows, _ := f.GetRows("Sheet1")
	for _, row := range rows {
		for _, colCell := range row {
			cols = append(cols, colCell)
		}
		break
	}
	return cols, nil
}

func (e *Excelservice) LoadFile(att_id uint, name string) error {
	filepath := fmt.Sprintf("%s/%s", path.Public("excel"), name)
	//读取文件内容
	file, err := os.Open(filepath)
	if err != nil {
		return err
	}
	f, err := excelize.OpenReader(file)
	defer f.Close()
	if err != nil {
		return err
	}
	//获取第一个sheet
	sheetName := f.GetSheetName(0)
	rows, err := f.GetRows(sheetName)
	if err != nil {
		return err
	}
	//获取表头
	for index, row := range rows {
		//从第三行开始读取，同时如果该行为合并了单元格，跳过
		if index <= 2 {
			continue
		}
		//如果row中存在合并列的情况，跳过
		if len(row) < 6 {
			continue
		}
	}
	return nil
}

func (*Excelservice) extractNumberAndText(data string) []string {
	var numericRegex = regexp.MustCompile(`(\d+)`)
	var nonNumericRegex = regexp.MustCompile(`\D+`)

	numericMatches := numericRegex.FindStringSubmatch(data)
	nonNumericMatches := nonNumericRegex.FindStringSubmatch(data)

	var numeric, nonNumeric string

	if len(numericMatches) > 1 {
		numeric = numericMatches[1]
	}

	if len(nonNumericMatches) > 0 {
		nonNumeric = nonNumericMatches[0]
	}

	return []string{numeric, nonNumeric}
}
func (r *Excelservice) ParseProduct(ctx http.Context, file filesystem.File) (*models.Attachment, error) {
	name := file.GetClientOriginalName()
	extension := file.GetClientOriginalExtension()
	//获取当前年月2022-02
	yearMonth := fmt.Sprintf("%d-%02d", time.Now().Year(), time.Now().Month())
	//file, err := ctx.Request().File(name)
	remote_path, err := file.Disk("oss").Store(yearMonth)
	putFile, err := facades.Storage().Disk("excel").PutFile(yearMonth, file)

	if err != nil {
		return nil, err
	}
	//保存文件，返回保存路径
	user := models.User{}
	if err1 := facades.Auth(ctx).User(&user); err1 != nil {
		att := models.Attachment{
			Name:       name,
			Path:       putFile,
			Ext:        extension,
			RemotePath: remote_path,
			Type:       "oss+local",
		}

		err2 := facades.Orm().Query().Model(&models.Attachment{}).Create(&att)
		if err2 != nil {
			return nil, err2
		}

		return &att, nil
	}
	att := models.Attachment{
		Name:       name,
		Path:       putFile,
		UserID:     user.ID,
		Ext:        extension,
		RemotePath: remote_path,
		Type:       "oss+local",
	}
	err3 := facades.Orm().Query().Model(&models.Attachment{}).Create(&att)
	if err3 != nil {
		return nil, err3
	}
	filepath := fmt.Sprintf("%s/%s", path.Public("excel"), putFile)
	//读取文件内容
	fileData, err := os.Open(filepath)
	if err != nil {
		return nil, err
	}
	f, err := excelize.OpenReader(fileData)
	defer f.Close()
	if err != nil {
		return nil, err
	}
	//获取第一个sheet
	sheetName := f.GetSheetName(0)
	rows, err := f.GetRows(sheetName)
	if err != nil {
		return nil, err
	}
	//获取表头
	header := rows[0]
	if !r.matchProductHeader(header) {
		return nil, errors.New("表头错误")
	}
	for index, row := range rows {
		//从第三行开始读取，同时如果该行为合并了单元格，跳过
		if index <= 1 {
			continue
		}
		if len(row) < len(header) {
			// 创建一个新的切片来填充列数
			newRow := make([]string, len(header))
			copy(newRow, row)
			// 填充剩余的单元格
			for i := len(row); i < len(header); i++ {
				newRow[i] = "" // 这里可以根据需要修改填充的值
			}
			row = newRow
		}
		facades.Orm().Query().Model(&models.Product{}).Create(&models.Product{
			Code:           row[0],
			Name:           row[1],
			Spec:           row[2],
			Store_Condi:    row[3],
			ProductionDate: carbon.NewDateTime(carbon.Parse(row[4])),
			ShelfLife:      cast.ToFloat64(row[5]),
			Unit:           row[6],
			Price:          cast.ToFloat64(row[7]),
			SalePrice:      cast.ToFloat64(row[8]),
			SupplierID:     1,
			Description:    row[10],
		})
	}
	return &att, nil
}
func (r *Excelservice) ParseMaterial(ctx http.Context, file filesystem.File) (*models.Attachment, error) {
	name := file.GetClientOriginalName()
	extension := file.GetClientOriginalExtension()
	//获取当前年月2022-02
	yearMonth := fmt.Sprintf("%d-%02d", time.Now().Year(), time.Now().Month())
	//file, err := ctx.Request().File(name)
	remote_path, err := file.Disk("oss").Store(yearMonth)
	putFile, err := facades.Storage().Disk("excel").PutFile(yearMonth, file)

	if err != nil {
		return nil, err
	}
	//保存文件，返回保存路径
	user := models.User{}
	if err1 := facades.Auth(ctx).User(&user); err1 != nil {
		att := models.Attachment{
			Name:       name,
			Path:       putFile,
			Ext:        extension,
			Type:       "oss+local",
			RemotePath: remote_path,
		}

		err2 := facades.Orm().Query().Model(&models.Attachment{}).Create(&att)
		if err2 != nil {
			return nil, err2
		}

		return &att, nil
	}
	att := models.Attachment{
		Name:       name,
		Path:       putFile,
		UserID:     user.ID,
		RemotePath: remote_path,
		Type:       "oss+local",
		Ext:        extension,
	}
	err3 := facades.Orm().Query().Model(&models.Attachment{}).Create(&att)
	if err3 != nil {
		return nil, err3
	}

	//新增历史报价单
	facades.Orm().Query().Model(&models.HistoryQuote{}).Create(&models.HistoryQuote{
		Name:        name,
		DownloadUrl: fmt.Sprintf("%s/%s", facades.Config().Env("ALIYUN_URL"), remote_path),
	})
	filepath := fmt.Sprintf("%s/%s", path.Public("excel"), putFile)
	//读取文件内容
	fileData, err := os.Open(filepath)
	if err != nil {
		return nil, err
	}
	f, err := excelize.OpenReader(fileData)
	defer f.Close()
	if err != nil {
		return nil, err
	}
	//获取第一个sheet
	sheetName := f.GetSheetName(0)
	rows, err := f.GetRows(sheetName)
	if err != nil {
		return nil, err
	}
	//获取表头
	header := rows[0]
	if !r.matchMaterialHeader(header) {
		return nil, errors.New("表头错误")
	}
	for index, row := range rows {
		//从第三行开始读取，同时如果该行为合并了单元格，跳过
		if index <= 1 {
			continue
		}
		if len(row) < len(header) {
			// 创建一个新的切片来填充列数
			newRow := make([]string, len(header))
			copy(newRow, row)
			// 填充剩余的单元格
			for i := len(row); i < len(header); i++ {
				newRow[i] = "" // 这里可以根据需要修改填充的值
			}
			row = newRow
		}
		facades.Orm().Query().Model(&models.Material{}).Create(&models.Material{
			Code:           row[0],
			Name:           row[1],
			Spec:           row[2],
			Store_Condi:    row[3],
			ProductionDate: carbon.NewDateTime(carbon.Parse(row[4])),
			ShelfLife:      cast.ToFloat64(row[5]),
			Unit:           row[6],
			Price:          cast.ToFloat64(row[7]),
			SupplierID:     3,
			Description:    row[9],
		})
	}
	return &att, nil
}

func (r *Excelservice) matchProductHeader(file_header []string) bool {
	headers := []string{"商品条码", "品名", "规格", "保存条件", "生产日期", "质保期", "单位", "供价", "建议售价", "供应商", "备注"}
	//如果file_header与headers不同返回false
	if len(file_header) != len(headers) {
		return false
	}
	for i, header := range headers {
		if header != file_header[i] {
			return false
		}
	}
	return true
}

func (r *Excelservice) matchMaterialHeader(file_header []string) bool {
	headers := []string{"商品条码", "品名", "规格", "保存条件", "生产日期", "质保期", "单位", "供价", "供应商", "备注"}
	//如果file_header与headers不同返回false
	if len(file_header) != len(headers) {
		return false
	}
	for i, header := range headers {
		if header != file_header[i] {
			return false
		}
	}
	return true
}
